SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_student
-- ----------------------------
DROP TABLE IF EXISTS `tb_student`;
CREATE TABLE `tb_student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `student_no` varchar(32) NOT NULL COMMENT '学号，唯一',
  `first_name` varchar(64) NOT NULL COMMENT '名',
  `last_name` varchar(64) NOT NULL COMMENT '姓氏',
  `gender` char(1) NOT NULL COMMENT '性别 M/F',
  `age` int(11) NOT NULL COMMENT '年龄',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `grade_id` bigint(20) NOT NULL COMMENT '年级ID',
  `class_id` bigint(20) NOT NULL COMMENT '班级ID',
  `phone` varchar(32) DEFAULT NULL COMMENT '电话',
  `email` varchar(128) DEFAULT NULL COMMENT '邮箱',
  `address` varchar(256) DEFAULT NULL COMMENT '家庭住址',
  `avatar_url` varchar(256) DEFAULT NULL COMMENT '头像URL',
  `status` tinyint(1) DEFAULT 0 COMMENT '状态 0=正常 1=禁用',
  `create_time` datetime DEFAULT current_timestamp() COMMENT '创建时间',
  `update_time` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新时间',
  `remark` varchar(256) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ak_student_no` (`student_no`),
  KEY `idx_grade_id` (`grade_id`),
  KEY `idx_class_id` (`class_id`),
  KEY `idx_status` (`status`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci COMMENT='学生信息表';

-- ----------------------------
-- Table structure for tb_score
-- ----------------------------
DROP TABLE IF EXISTS `tb_score`;
CREATE TABLE `tb_score` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `student_no` bigint(20) NOT NULL COMMENT '学生学号',
  `course_id` bigint(20) NOT NULL COMMENT '课程ID',
  `grade_id` bigint(20) NOT NULL COMMENT '年级ID',
  `class_id` bigint(20) NOT NULL COMMENT '班级ID',
  `term_name` tinyint(1) NOT NULL COMMENT '学期名称(1=上学期 2=下学期)',
  `score` decimal(5,2) NOT NULL COMMENT '成绩',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ak_student_no_grade_id_class_id_course_id_term_name` (`student_no`, `grade_id`, `class_id`, `course_id`, `term_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生成绩表';

-- ----------------------------
-- Table structure for tb_course
-- ----------------------------
DROP TABLE IF EXISTS `tb_course`;
CREATE TABLE `tb_course` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '课程ID',
  `course_name` varchar(64) NOT NULL COMMENT '课程名称',
  `course_desc` varchar(256) DEFAULT NULL COMMENT '课程描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表';

-- ----------------------------
-- Table structure for tb_student
-- ----------------------------

-- ----------------------------
-- Table structure for tb_grade
-- ----------------------------
DROP TABLE IF EXISTS `tb_grade`;
CREATE TABLE `tb_grade` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
  `grade_name` varchar(64) NOT NULL COMMENT '年级名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='年级表';

-- ----------------------------
-- Table structure for tb_class
-- ----------------------------
DROP TABLE IF EXISTS `tb_class`;
CREATE TABLE `tb_class` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '班级ID',
  `class_name` varchar(64) NOT NULL COMMENT '班级名称',
  `grade_id` bigint(20) NOT NULL COMMENT '年级ID',
  `head_teacher` varchar(64) DEFAULT NULL COMMENT '班主任姓名',
  PRIMARY KEY (`id`),
  KEY `idx_grade_id` (`grade_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班级表';

-- ----------------------------
-- Records of tb_admin
-- ----------------------------
BEGIN;
INSERT INTO `tb_admin` (`id`, `username`, `password`, `salt`, `real_name`, `phone`, `email`, `status`, `last_login_time`, `create_time`, `update_time`, `remark`) VALUES (1, 'admin', 'dd6280f074fd17e557cd32c985bdae2e2fa58aedc56d261644509efe951f95e8', '4gGMVB0GEz', '阳老师', '19918787878', 'vsiryxm@qq.com', 0, '2025-09-10 15:06:09', '2025-09-10 15:05:49', '2025-09-12 03:14:30', '这是廖老师的账号');
COMMIT;

-- ----------------------------
-- Table structure for tb_admin
-- ----------------------------
DROP TABLE IF EXISTS `tb_admin`;
CREATE TABLE `tb_admin` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(32) NOT NULL COMMENT '登录用户名，唯一',
  `password` varchar(128) NOT NULL COMMENT '登录密码（加密存储）',
  `salt` varchar(10) NOT NULL COMMENT '密码盐值',
  `real_name` varchar(64) DEFAULT NULL COMMENT '真实姓名',
  `phone` varchar(32) DEFAULT NULL COMMENT '电话',
  `email` varchar(128) DEFAULT NULL COMMENT '邮箱',
  `status` tinyint(1) DEFAULT 0 COMMENT '状态 0=正常 1=禁用',
  `last_login_time` datetime DEFAULT NULL COMMENT '最后登录时间',
  `create_time` datetime DEFAULT current_timestamp() COMMENT '创建时间',
  `update_time` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新时间',
  `remark` varchar(256) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ak_username` (`username`),
  KEY `idx_status` (`status`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci COMMENT='管理员信息表';

SET FOREIGN_KEY_CHECKS = 1;